package com.cci.kangdao.dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.mybatis.spring.annotation.MapperScan;

import java.util.List;
import java.util.Map;

@MapperScan
public interface SyncServiceTechnicianMsgToCRMTaskDao {


    //获取被XCEC授权的服务站下所有用户信息
    @Select("select a.ADUserID as appUserID,a.Contact as contactName,a.Phone as phone,e.StationName as stationName,f.BusinessNo as stationNo,1 as requstStatus,0 as syncCount,\n" +
            "d.RolesName as position\n" +
            "from User_T a\n" +
            "left join UserHasRoles_T b on a.ID=b.UserID\n" +
            "left join UserGroupHasRole_T c on c.ID = b.RolesID\n" +
            "left join Roles_T d on c.RoleId = d.ID\n" +
            "left join ServiceStation_T e on a.ServiceStationID = e.ID\n" +
            "left join SSRepair_T f on e.ID = f.ServiceStationID\n" +
            "where  a.Flag = 0 and b.Flag = 0\n" +
            "and c.Flag = 0 and d.Flag = 0 and\n" +
            "a.ServiceStationID in (select ServiceStationID from SSRepair_T where CompanyID = #{companyId} and Flag = 0)\n" +
            "and f.Flag = 0 and f.CompanyID =#{companyId}")
    List<Map<String,Object>> getServiceTechnicianMsgList(@Param("companyId") long companyId);


    //获取XCEC每天新增的服务站用户信息
    @Select("select d.ADUserID as appUserID,d.Contact as contactName,d.Phone as phone,e.StationName as stationName,f.BusinessNo as stationNo,a.RolesName as position,1 as requstStatus,0 as syncCount  from Roles_T a\n" +
            "inner join UserGroupHasRole_T b on a.ID =b.RoleId\n" +
            "inner join UserHasRoles_T c on b.ID = c.RolesID\n" +
            "inner join User_T d on c.UserID = d.ID\n" +
            "inner join ServiceStation_T e on d.ServiceStationID = e.ID\n" +
            "inner join SSRepair_T f on e.ID = f.ServiceStationID\n" +
            "where a.Flag = 0 and b.Flag = 0\n" +
            "and c.Flag = 0 and d.Flag = 0 and left(c.Systime,10) = #{date}\n" +
            "and d.ServiceStationID in (select ServiceStationID from SSRepair_T where CompanyID = #{companyId} and Flag = 0)\n" +
            "and f.Flag=0 and f.CompanyID = #{companyId}\n" +
            "order by c.Systime desc")
    List<Map<String,Object>> getNewAddServiceTechnicianMsgList(@Param("companyId") long companyId,@Param("date")String date);


    //获取XCEC每天修改的服务站用户信息
   @Select(";with t0 as (\n" +
           "select d.ADUserID as appUserID,d.Contact as contactName,d.Phone as phone,e.StationName as stationName,f.BusinessNo as stationNo,a.RolesName as position,3 as requstStatus,0 as syncCount  \n" +
           ",c.Systime,c.Flag as flag\n" +
           "from Roles_T a\n" +
           "inner join UserGroupHasRole_T b on a.ID =b.RoleId\n" +
           "inner join UserHasRoles_T c on b.ID = c.RolesID\n" +
           "inner join User_T d on c.UserID = d.ID\n" +
           "inner join ServiceStation_T e on d.ServiceStationID = e.ID\n" +
           "inner join SSRepair_T f on e.ID = f.ServiceStationID\n" +
           "where a.Flag = 0 and b.Flag = 0\n" +
           "and c.Flag = 0 and d.Flag = 0 and left(c.Systime,10) = #{date}\n" +
           "and d.ServiceStationID in (select ServiceStationID from SSRepair_T where CompanyID = #{companyId} and Flag = 0)\n" +
           "and d.ID in(select distinct TID from SystemLogs_T \n" +
           "where Flag = 0 and TName = 'User_T' and ActionName = 'updateData' and left(Systime,10)=#{date})\n" +
           "and f.Flag=0 and f.CompanyID = #{companyId}\n" +
           "),t1 as (select * \n" +
           ",row_number() over(partition by phone order by systime desc) as rn\n" +
           "from t0\n" +
           ")select * from t1 where rn=1 and flag=0")
    List<Map<String,Object>> getUpdateServiceTechnicianUserMsgList(@Param("companyId") long companyId,@Param("date")String date);

    //获取XCEC角色和用户关系表中服务技师角色被删除的数据
    @Select(";with t0 as (\n" +
            "select d.ADUserID as appUserID,d.Contact as contactName,d.Phone as phone,e.StationName as stationName,f.BusinessNo as stationNo,a.RolesName as position,2 as requstStatus,0 as syncCount  \n" +
            ",c.Systime,c.Flag as flag\n" +
            "from Roles_T a\n" +
            "inner join UserGroupHasRole_T b on a.ID =b.RoleId\n" +
            "inner join UserHasRoles_T c on b.ID = c.RolesID\n" +
            "inner join User_T d on c.UserID = d.ID\n" +
            "inner join ServiceStation_T e on d.ServiceStationID = e.ID\n" +
            "inner join SSRepair_T f on e.ID = f.ServiceStationID\n" +
            "where a.Flag = 0 and b.Flag = 0\n" +
            "and d.Flag = 0 and left(c.Systime,10) = #{date}\n" +
            "and d.ServiceStationID in (select ServiceStationID from SSRepair_T where CompanyID =#{companyId} and Flag = 0)\n" +
            "and f.Flag = 0 and f.CompanyID = #{companyId}\n" +
            "),t1 as (select * \n" +
            ",row_number() over(partition by phone order by systime desc) as rn\n" +
            "from t0\n" +
            ")select * from t1 where rn=1 and flag=-1")
    List<Map<String,Object>> getDeleteUserHasRolesMsgList(@Param("companyId") long companyId,@Param("date")String date);


    //获取XCEC下服务技师在用户表用户被作废的数据
    @Select("select d.ADUserID,d.Contact as contactName,d.Phone as phone,e.StationName as stationName,f.BusinessNo as stationNo,a.RolesName as position,2 as requstStatus,0 as syncCount from Roles_T a\n" +
            "inner join UserGroupHasRole_T b on a.ID =b.RoleId\n" +
            "inner join UserHasRoles_T c on b.ID = c.RolesID\n" +
            "inner join User_T d on c.UserID = d.ID\n" +
            "inner join ServiceStation_T e on d.ServiceStationID = e.ID\n" +
            "inner join SSRepair_T f on e.ID = f.ServiceStationID\n" +
            "where a.Flag = 0 and b.Flag = 0\n" +
            "and c.Flag =0 and d.Flag = -1 and left(d.Systime,10) = #{date}\n" +
            "and d.ServiceStationID in (select ServiceStationID from SSRepair_T where CompanyID = #{companyId} and Flag = 0)\n" +
            "and f.Flag = 0 and f.CompanyID = #{companyId}\n" +
            "order by d.Systime desc")
    List<Map<String,Object>> getDeleteUserMsgList(@Param("companyId") long companyId,@Param("date")String date);
}
